import * as GC from "@grapecity/spread-sheets";
import "@grapecity/spread-sheets-io"
import "@grapecity/spread-sheets-shapes"
import "@grapecity/spread-sheets-resources-zh"
import "@grapecity/spread-sheets-designer-resources-cn"
import "@grapecity/spread-sheets-designer"

import { data as data1, year as year1, month as month1 } from "../data1.js"
import { data as data2, year as year2, month as month2 } from "../data2.js"
import { data as data3, year as year3, month as month3 } from "../data3.js"


let datas = [{
    data: data1,
    month: month1,
    year: year1
}, {
    data: data2,
    month: month2,
    year: year2
}, {
    data: data3,
    month: month3,
    year: year3
}]
let tempData
GC.Spread.Common.CultureManager.culture("zh-cn");
// designer = new GC.Spread.Sheets.Designer.Designer("designer-container")
// spread = designer.getWorkbook()
spread = new GC.Spread.Sheets.Workbook("designer-container")

let oldF = spread.contextMenu.onOpenMenu
spread.contextMenu.onOpenMenu = function (menuData, itemsDataForShown, hitInfo, spread) {
    itemsDataForShown.push({
        text: "插入子级",
        name: "customContextMenu",
        command: "addChildCommand"
    })
    // 在第二行不可用
    let sel = spread.getActiveSheet().getSelections()[0]
    if (sel.row == 1) {
        itemsDataForShown.splice(0, itemsDataForShown.length)
    }
    oldF.apply(this, arguments)
}

spread.commandManager().register("addChildCommand", {
    canUndo: false,
    execute: function (spread, options) {
        let sheet = spread.getActiveSheet()

        let selections = sheet.getSelections()
        if (selections.length > 1 || selections[0].rowCount > 1) {
            alert("插入子级时只能选择一行")
            return
        }
        let row = selections[0].row
        let table = sheet.tables.find(row, 0) || sheet.tables.find(row - 1, 0)
        let path = table.bindingPath()
        let source = sheet.getDataSource().getSource()
        let arr = source[path]
        let range = table.range()
        sheet.suspendPaint()
        sheet.suspendCalcService()

        let tree = convertFlat2Tree(source)
        let cur_data = arr[row - range.row]
        let sourceParentPath = getItemParentPath(source[path], cur_data, [cur_data.no])
        // 按照sourceParentPath，依次找到tree中当前选择的数据
        let dataItem = findItemByNo(tree[path], sourceParentPath.pop())
        while (true) {
            let tempItem = findItemByNo(dataItem.children, sourceParentPath.pop())
            if (tempItem) {
                dataItem = tempItem
            } else {
                break
            }
        }
        if (!dataItem.hasChildren) {
            dataItem.children = []
        }
        let new_data = {
            no: cur_data.no + "." + (dataItem.children.length + 1),
            name: "子级标题"
        }
        dataItem.children.push(new_data)
        source = convertTree2Flat(tree)
        sheet.tables.resize(table, new GC.Spread.Sheets.Range(range.row, range.col, range.rowCount + 1, range.colCount))
        // resize会导致数据源多出一个空对象，去掉它
        source[path] = source[path].filter(v => {
            return JSON.stringify(v) != "{}"
        })
        clearAllFormula(sheet)
        preCleanData(source)
        
        sheet.setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource(source))
        sheet.repaint()

        setStyles()
        recalculateSum()
        addInvalidComments()

        sheet.resumeCalcService()
        sheet.resumePaint()
    }
})


let file
let xhr = new XMLHttpRequest()
xhr.open("get", "./t.sjs")
xhr.responseType = "blob"
xhr.addEventListener("loadend", function () {
    if (this.readyState == 4 && this.status == 200) {
        file = new File([this.response], "t.sjs")
        spread.open(file, function () {
            refresh()
        })
    }
})
xhr.send()

document.getElementById("loadAnother").addEventListener("click", function () {
    spread.open(file, function () {
        refresh()
    })
})

// 所有数值对应的键值，这里必须和页面上的字段顺序一致
let value_keys = ["this_year_deposit", "last_year_deposit", "this_year_month_deposit", "last_year_month_deposit", "this_year_loan", "last_year_loan", "this_year_month_loan", "last_year_month_loan"]

function refresh() {
    tempData = datas.shift()
    preCleanData(tempData.data)
    setData(tempData.data)
    setStyles()
    bindEvents()
    recalculateSum()
    addInvalidComments()
    changeCellYearMonth()
    datas.push(tempData)
}

// 导出为excel
document.getElementById("exportExcel").addEventListener("click", function () {
    spread.export(function (blob) {
        saveAs(blob, "t.xlsx")
    }, function () { }, {
        fileType: GC.Spread.Sheets.FileType.excel,
        includeBindingSource: true,
    })
})

function preCleanData(_data) {
    Object.keys(_data).forEach(key => {
        _data[key].forEach((d, i) => {
            // 控制name缩进
            d.no = String(d.no)
            let indentLvl = d.no.split(".").length - 1
            let space = ""
            while (indentLvl > 0) {
                space += "      "
                indentLvl--
            }
            d.name = d.name?.trim() || ""
            d.name = space + d.name
            // 判断哪些行有子级
            d.hasChildren = false
            if (_data[key][i + 1]) {
                let nextData = _data[key][i + 1]
                if (nextData.no.indexOf(d.no) == 0) {
                    d.hasChildren = true
                }
            }
        })

        // 计算添加parent属性，方便后续找子集
        _data[key].forEach(v => {
            v.parent = null
            v.level = v.no.split(".").length
        })
        for (let i = 0; i < _data[key].length; i++) {
            let p1 = _data[key][i]
            for (let j = i; j < _data[key].length; j++) {
                let p2 = _data[key][j]
                if (p2.no.indexOf(p1.no) == 0 && p2.level - p1.level == 1) {
                    p2.parent = p1.no
                }
            }
        }
    })
}

// 将拉平的数据转换为树形
function convertFlat2Tree(data) {
    let data_copy = JSON.parse(JSON.stringify(data))
    let tree = {}
    Object.keys(data_copy).forEach(key => {
        tree[key] = []
        data_copy[key].forEach(d => {
            if (!d.parent) {
                d.children = []
                tree[key].push(d)
            } else {
                try {
                    let parent_data = findParent(tree[key], d.parent)
                    if (!parent_data.children) {
                        parent_data.children = []
                    }
                    parent_data.children.push(d)
                } catch (e) {
                    console.log(e)
                }
            }
        })
    })

    return tree

    function findParent(arr, p_no) {
        for (let i = 0; i < arr.length; i++) {
            let d = arr[i]
            if (d.no == p_no) {
                return d
            } else if (p_no.indexOf(d.no) == 0) {
                return findParent(d.children, p_no)
            } else {
                continue
            }
        }
    }
}
// 将树形数据拉平
function convertTree2Flat(tree) {
    let flat = {}
    Object.keys(tree).forEach(key => {
        flat[key] = []
        tree[key].forEach(d => {
            recursionGetItem(d, flat[key])
        })
    })
    return flat
    function recursionGetItem(item, result) {
        result.push(item)
        let c = item.children?.shift()
        while (c) {
            recursionGetItem(c, result)
            c = item.children.shift()
        }
        delete item.children
    }
}

// 重新排列数据的序号
function resortDataNo(treeData) {
    Object.keys(treeData).forEach(key => {
        let index = 1
        treeData[key].forEach(d => {
            let no_arr = d.no.split(".")
            no_arr.pop()
            let no_str = no_arr.join(".")
            recursionSortItem(d, no_str, index)
            index++
        })
    })
    function recursionSortItem(item, p_no, index) {
        if (index) {
            item.no = p_no ? p_no + "." + index : index + ""
        }
        if (!item.children) {
            item.children = []
        }
        item.children.forEach((v, i) => {
            v.no = item.no + "." + Number(i + 1)
            recursionSortItem(v, v.no)
        })
    }
}

function setData(d) {
    let sheet = spread.getActiveSheet()
    sheet.tables.all().forEach(t => {
        t.expandBoundRows(true)
    })
    sheet.setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource(d))
}

function setStyles() {
    spread.suspendPaint()
    let sheet = spread.getActiveSheet()

    let usedRange = sheet.getUsedRange(GC.Spread.Sheets.UsedRangeType.table)
    let borderLine = new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin)
    // 添加边框线
    for (let r = usedRange.row; r < usedRange.row + usedRange.rowCount; r++) {
        for (let c = usedRange.col; c < usedRange.col + usedRange.colCount; c++) {
            let tempStyle = sheet.getActualStyle(r, c) || new GC.Spread.Sheets.Style()
            tempStyle.borderLeft = borderLine
            tempStyle.borderTop = borderLine
            tempStyle.borderRight = borderLine
            tempStyle.borderBottom = borderLine
            sheet.setStyle(r, c, tempStyle)
        }
    }

    // 自动合并的居中显示，这里要用配置来取位置，暂时写死
    let ft = sheet.tables.all()[0]
    let start_c = ft.startColumn()
    let start_r = ft.startRow()
    let end_r = ft.endRow()
    let style = new GC.Spread.Sheets.Style()
    style.vAlign = GC.Spread.Sheets.VerticalAlign.center

    style.borderLeft = borderLine
    style.borderTop = borderLine
    style.borderRight = borderLine
    style.borderBottom = borderLine

    sheet.getRange(start_r, start_c, end_r - start_r, 1).setStyle(style)

    sheet.tables.all().forEach(table => {
        let tRange = table.range()
        if (table.showHeader()) {
            tRange.row++
        }
        if (table.showFooter()) {
            tRange.rowCount--
        }
        let path = table.bindingPath()
        let _data = sheet.getDataSource()?.getSource()[path]
        for (let r = tRange.row; r < tRange.row + tRange.rowCount; r++) {
            let lineRange = sheet.getRange(r, tRange.col, 1, tRange.colCount)
            if (_data[r - tRange.row]?.hasChildren) {
                // 有子级的行 字体加粗 背景变色
                let style = new GC.Spread.Sheets.Style()
                style.font = "bold 11pt Calibri"

                style.borderLeft = borderLine
                style.borderTop = borderLine
                style.borderRight = borderLine
                style.borderBottom = borderLine
                // 不同层级 背景色逻辑
                switch (_data[r - tRange.row].level) {
                    case 1:
                        style.backColor = "#595959"
                        break
                    case 2:
                        style.backColor = "#7f7f7f"
                        break
                    case 3:
                        style.backColor = "#a5a5a5"
                        break
                    case 4:
                        style.backColor = "#bfbfbf"
                        break
                    case 5:
                        style.backColor = "#d8d8d8"
                        break
                    default:
                        break

                }
                lineRange.setStyle(style)
                // 在第一列加上展开、收起的图标，这里用加减号代替
                let cell = new lvlCell()
                sheet.setCellType(r, tRange.col, cell)
            } else {
                // 没有子级的行 字体加正常 背景白色
                let style = new GC.Spread.Sheets.Style()
                style.font = "normal 11pt Calibri"

                style.borderLeft = borderLine
                style.borderTop = borderLine
                style.borderRight = borderLine
                style.borderBottom = borderLine
                style.backColor = ""
                lineRange.setStyle(style)
            }
        }
    })

    spread.resumePaint()
}

function clearAllFormula(sheet) {
    let searchCondition = new GC.Spread.Sheets.Search.SearchCondition()
    searchCondition.searchTarget = GC.Spread.Sheets.Search.SearchFoundFlags.cellFormula
    searchCondition.searchFlags = GC.Spread.Sheets.Search.SearchFlags.ignoreCase
    searchCondition.rowStart = 0
    searchCondition.columnStart = 0
    searchCondition.searchString = "sum"
    let searchResult = sheet.search(searchCondition)

    while (true) {
        if (!searchResult.foundString) {
            break
        }
        // 删除的时候可能导致公式出错，先把所有公式都置为空，删除结束后重新设置公式
        sheet.setFormula(searchResult.foundRowIndex, searchResult.foundColumnIndex, null)
        sheet.setValue(searchResult.foundRowIndex, searchResult.foundColumnIndex, 0)
        searchCondition.rowStart = searchResult.foundRowIndex
        searchCondition.columnStart = searchResult.foundColumnIndex + 1
        searchResult = sheet.search(searchCondition)
    }
}

function bindEvents() {
    // 禁止有子级的单元格进入编辑状态，禁止粘贴、拖动可以用类似的方法处理
    spread.getActiveSheet().bind(GC.Spread.Sheets.Events.EditStarting, function (e, arg) {
        let table = arg.sheet.tables.find(arg.row, arg.col)
        if (!table) {
            arg.cancel = true
            return
        }
        let range = table.range()
        if (range.col == arg.col || range.col == arg.col + 1) {
            arg.cancel = true
            return
        }
        if (table.showHeader()) {
            range.row++
        }
        if (table.showFooter()) {
            range.rowCount--
        }
        let path = table.bindingPath()
        let _data = arg.sheet.getDataSource()?.getSource()[path]
        if (_data[arg.row - range.row].hasChildren) {
            arg.cancel = true
        }
    })

    // 点击第一列 折叠/展开
    spread.getActiveSheet().bind(GC.Spread.Sheets.Events.CellClick, function (e, arg) {
        if (arg.sheetArea != GC.Spread.Sheets.SheetArea.viewport) {
            return
        }
        spread.suspendPaint()
        let cell = arg.sheet.getCellType(arg.row, arg.col)
        if (cell.typeName == "lvlCell") {
            let lvl_no = arg.sheet.getValue(arg.row, arg.col)
            let r = arg.row + 1
            while (true) {
                let value = arg.sheet.getValue(r, arg.col)
                if (!value) {
                    break
                }
                if (value.indexOf(lvl_no) == 0) {
                    arg.sheet.setRowVisible(r, !cell.isOpen)
                    let curCell = arg.sheet.getCellType(r, arg.col)
                    curCell.isOpen = !cell.isOpen
                    r++
                } else {
                    break
                }
            }
        }
        cell.isOpen = !cell.isOpen
        spread.resumePaint()
    })

    spread.getActiveSheet().bind(GC.Spread.Sheets.Events.ValueChanged, function (e, arg) {
        addInvalidComments()
    })

    // 仅有可编辑的区域可以被粘贴
    spread.bind(GC.Spread.Sheets.Events.ClipboardPasting, function (sender, args) {
        args.sheet.suspendPaint()
        // 禁止默认的粘贴，自己处理粘贴逻辑
        args.cancel = true
        let text = args.pasteData.text
        let arr = text.split("\r\n").map(v => {
            return v.split("\t")
        })
        for (let r = 0; r < args.cellRange.rowCount; r++) {
            for (let c = 0; c < args.cellRange.colCount; c++) {
                let table = args.sheet.tables.find(args.cellRange.row, args.cellRange.col)
                let range = table.range()
                let path = table.bindingPath()
                let _data = args.sheet.getDataSource()?.getSource()[path]
                if (!_data[args.cellRange.row + r - range.row].hasChildren) {
                    let value = arr[r][c]
                    if (Number(value)) {
                        value = Number(value)
                    }
                    args.sheet.setValue(args.cellRange.row + r, args.cellRange.col + c, value)
                }
            }
        }
        args.sheet.resumePaint()
    });

    let deleted_no = []

    function rowChangingCallback(e, arg) {
        let table
        if (arg.propertyName == "deleteRows") {
            table = arg.sheet.tables.find(arg.row, 0)
            // 不允许在table以外删除和新增行
            if (!table) {
                arg.cancel = true
                return
            }
        }
        if (arg.propertyName == "addRows") {
            table = arg.sheet.tables.find(arg.row - 1, 0)
            // 不允许在table以外删除和新增行
            if (!table) {
                arg.cancel = true
                return
            }
        }

        // 删除行在RowChanging事件处理，因为如果在RowChanged里写，被删除的数据已经消失了
        if (arg.propertyName == "deleteRows") {

            let table2 = arg.sheet.tables.find(arg.row, 0)
            if (!table2) {
                arg.cancel = true
                return
            }
            arg.sheet.suspendPaint()
            arg.sheet.suspendCalcService()


            let path = table.bindingPath()
            let source = arg.sheet.getDataSource().getSource()
            let arr = source[path]
            let range = table.range()

            for (let i = arg.row - range.row; i < arg.row + arg.count - range.row; i++) {
                deleted_no.push(arr[i].no)
            }
            // 清除所有公式
            clearAllFormula(arg.sheet)

        }
    }
    spread.getActiveSheet().bind(GC.Spread.Sheets.Events.RowChanging, rowChangingCallback);

    function rowChangedCallback(e, arg) {
        let table = arg.sheet.tables.find(arg.row, 0) || arg.sheet.tables.find(arg.row - 1, 0)
        let path = table.bindingPath()
        let source = arg.sheet.getDataSource().getSource()
        let arr = source[path]
        let range = table.range()
        arg.sheet.suspendPaint()
        arg.sheet.suspendCalcService()

        // 处理添加行逻辑
        // 删除行在RowChanging事件处理，因为如果在RowChanged里写，被删除的数据已经消失了
        if (arg.propertyName == "addRows") {
            let prev_data = arr[arg.row + arg.count - range.row]
            let isLast = false
            if (!prev_data) {
                isLast = true
                prev_data = arr[arr.length - 1]
            }
            for (let i = 0; i < arg.count; i++) {
                if (!arr[arg.row + i - range.row]) {
                    arr[arg.row + i - range.row] = {}
                }
                arr[arg.row + i - range.row].hasChildren = false
                arr[arg.row + i - range.row].parent = prev_data.parent
                arr[arg.row + i - range.row].name = "同级名称"
                arr[arg.row + i - range.row].level = prev_data.level

                let prev_no_arr = prev_data.no.split(".")
                prev_no_arr[prev_no_arr.length - 1] = Number(prev_no_arr[prev_no_arr.length - 1]) + i
                arr[arg.row + i - range.row].no = prev_no_arr.join(".")
            }
            let prev_level = prev_data.level
            let prev_no = prev_data.no
            let prev_index = Number(prev_no.split(".").pop())
            if (isLast) {
                for (let i = range.rowCount; i < arr.length; i++) {
                    prev_index++
                    let temp_arr = prev_no.split(".")
                    temp_arr.splice(temp_arr.length - 1, 1, prev_index)
                    arr[i].no = temp_arr.join(".")
                }
            } else {
                let cur_p_no
                for (let i = arg.row - range.row; i < arr.length; i++) {
                    // 如果遍历的项的level小于之前的level，证明遍历到外层了，后续不再遍历
                    if (arr[i].level < prev_level) {
                        break
                    }
                    if(i < arg.row - range.row + arg.count) {
                        continue
                    }
                    if (i >= arg.row - range.row + arg.count && arr[i].level == prev_level) {
                        let cur_no_arr = arr[i].no.split(".")
                        // 被插入了N行，那么同级别的序号末位就要加N
                        cur_no_arr[cur_no_arr.length - 1] = Number(cur_no_arr[cur_no_arr.length - 1]) + arg.count
                        arr[i].no = cur_no_arr.join(".")
                        cur_p_no = arr[i].no
                        prev_index = 1
                        continue
                    }
                    let old_no = arr[i].no
                    let old_arr = old_no.split("")
                    old_arr.splice(0, cur_p_no.length, cur_p_no)
                    arr[i].no = old_arr.join("")
                    arr[i].parent = cur_p_no
                }
            }
            let treeData = convertFlat2Tree(source)
            resortDataNo(treeData)
            source = convertTree2Flat(treeData)
            preCleanData(source)
            if (isLast) {
                arg.sheet.tables.resize(table, new GC.Spread.Sheets.Range(range.row, range.col, range.rowCount + arg.count, range.colCount))
                // resize会导致数据源多出一个空对象，去掉它
                source[path] = arr.filter(v => {
                    return JSON.stringify(v) != "{}"
                })
            }
            arg.sheet.setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource(source))
        }

        if (arg.propertyName == "deleteRows") {
            arg.sheet.unbind(GC.Spread.Sheets.Events.RowChanging, rowChangingCallback)
            arg.sheet.unbind(GC.Spread.Sheets.Events.RowChanged, rowChangedCallback)

            // 如果找不到parent，就证明它的parent被删除了，它也应该被删除
            let cur_row = arg.row
            while (true) {
                let cur_data = arr[cur_row - range.row]
                if (!cur_data) {
                    break
                }
                if (deleted_no.indexOf(cur_data.parent) != -1) {
                    arg.sheet.deleteRows(cur_row, 1)
                    range = table.range()
                    deleted_no.push(cur_data.no)
                } else {
                    cur_row++
                }
                if (cur_row > range.row + range.rowCount) {
                    break
                }
            }

            let treeData = convertFlat2Tree(source)
            resortDataNo(treeData)
            let flatData = convertTree2Flat(treeData)
            preCleanData(flatData)
            arg.sheet.setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource(flatData))

            arg.sheet.bind(GC.Spread.Sheets.Events.RowChanging, rowChangingCallback)
            arg.sheet.bind(GC.Spread.Sheets.Events.RowChanged, rowChangedCallback)
        }
        deleted_no = []

        if (arg.propertyName == "addRows" || arg.propertyName == "deleteRows") {
            // preCleanData(source)
            setStyles()
            arg.sheet.repaint()
        }
        recalculateSum()
        addInvalidComments()
        arg.sheet.resumeCalcService()
        arg.sheet.resumePaint()
        // 删除的时候，在RowChanging里暂停了一次 没有放开，这里需要额外再继续绘制一次
        if (arg.propertyName == "deleteRows") {
            arg.sheet.resumeCalcService()
            arg.sheet.resumePaint()
        }
    }
    spread.getActiveSheet().bind(GC.Spread.Sheets.Events.RowChanged, rowChangedCallback);

    spread.getActiveSheet().bind(GC.Spread.Sheets.Events.DragDropBlock, function (e, arg) {
        arg.cancel = true
        // 每次只能拖一行
        if (arg.rowCount > 1) {
            alert("禁止多行拖拽")
            return
        }

        if (arg.colCount == arg.sheet.getColumnCount() && arg.fromCol == 0) {
            arg.fromCol = -1
        }
        // 必须整行拖，禁止选中一部分拖拽
        if (arg.fromCol != -1) {
            alert("只能整行拖拽")
            return
        }

        // 区分两种情况：
        // 第一种 直接拖拽，拖到目标行的子级
        // 第二种，按住ctrl shift拖拽，拖到目标行的同级

        let table = arg.sheet.tables.find(arg.fromRow, 0)
        let source = arg.sheet.getDataSource().getSource()
        let path = table.bindingPath()
        let range = table.range()

        let sourceData = source[path][arg.fromRow - range.row]
        let targetData
        if (arg.insert) {
            targetData = source[path][arg.toRow - range.row - 1]
        } else {
            targetData = source[path][arg.toRow - range.row]
        }
        if (arg.toRow < range.row || arg.toRow > range.row + range.rowCount) {
            alert("禁止拖动至表格之外")
            return
        }

        if (targetData && targetData.no.indexOf(sourceData.no) == 0) {
            alert("禁止拖动到当前行的子级")
            return
        }


        arg.sheet.suspendPaint()
        arg.sheet.suspendCalcService()

        let tree = convertFlat2Tree(source)
        let _source = getItemAndParent(tree, source, sourceData, path)
        let _target = getItemAndParent(tree, source, targetData, path)

        let sourceParentItem = _source.parent
        let sourceItem = _source.item

        let targetParentItem = _target.parent
        let targetItem = _target.item

        if (sourceParentItem.children) {
            sourceParentItem = sourceParentItem.children
        }
        if (targetParentItem.children) {
            targetParentItem = targetParentItem.children
        }
        // 直接拖拽到子级
        if (!arg.insert) {
            let sourceIndex = sourceParentItem.indexOf(sourceItem)
            targetItem.children.push(sourceParentItem.splice(sourceIndex, 1)[0])
        }

        // ctrl shift拖拽到同级
        if (arg.insert) {
            let targetIndex = targetParentItem.indexOf(targetItem)
            let sourceIndex = sourceParentItem.indexOf(sourceItem)
            // if (targetIndex == -1) {
            //     sourceItem.no = "1.1"
            // }
            if (targetParentItem == sourceParentItem) {
                if (targetIndex == -1) {
                    targetIndex = 0
                }
                targetParentItem.splice(targetIndex, 0, sourceParentItem.splice(sourceIndex, 1)[0])
            } else {
                targetParentItem.splice(targetIndex + 1, 0, sourceParentItem.splice(sourceIndex, 1)[0])
            }
        }
        clearAllFormula(arg.sheet)
        resortDataNo(tree)
        let flatData = convertTree2Flat(tree)
        preCleanData(flatData)
        arg.sheet.setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource(flatData))
        setStyles()
        recalculateSum()
        addInvalidComments()
        arg.sheet.resumeCalcService()
        arg.sheet.resumePaint()
    });
}

function getItemAndParent(tree, source, itemData, path) {
    if (!itemData) {
        return {
            item: undefined,
            parent: tree[path]
        }
    }

    let sourceParentPath = getItemParentPath(source[path], itemData, [itemData.no])
    let parentItem = tree[path]
    let dataItem = findItemByNo(tree[path], sourceParentPath.pop())
    let dataItemStack = [tree[path]]
    dataItemStack.push(dataItem)
    while (true) {
        let tempItem = findItemByNo(dataItem.children, sourceParentPath.pop())
        if (tempItem) {
            dataItem = tempItem
            dataItemStack.push(tempItem)
        } else {
            break
        }
    }
    parentItem = dataItemStack.pop()
    if (dataItemStack.length >= 1) {
        parentItem = dataItemStack.pop()
    }

    if (!dataItem.children) {
        dataItem.children = []
    }

    return {
        item: dataItem,
        parent: parentItem
    }
}

// 获取当前选择的数据的父级no数组，例如当前的no是1.2.3，则返回["1.2.3", "1.2", "1"]
function getItemParentPath(arr, item, result = []) {
    result.push(item.parent)
    for (let i = 0; i < arr.length; i++) {
        if (arr[i].no == item.parent) {
            return getItemParentPath(arr, arr[i], result)
        }
    }
    result.pop()
    return result
}

function findItemByNo(arr, no) {
    if (!arr) {
        return
    }
    for (let i = 0; i < arr.length; i++) {
        if (arr[i].no == no) {
            return arr[i]
        }
    }
}
// 重新计算合计值
function recalculateSum() {
    let sheet = spread.getActiveSheet()
    sheet.suspendPaint()
    sheet.suspendCalcService()
    sheet.suspendEvent()

    let source = sheet.getDataSource().getSource()
    sheet.tables.all().forEach(table => {
        let path = table.bindingPath()
        let range = table.range()
        let arr = source[path]

        let key2Column = {}
        for (let i = 0; i < range.colCount; i++) {
            let field = table.getColumnDataField(i)
            let index = value_keys.indexOf(field)
            if (index > -1) {
                key2Column[value_keys[index]] = i + range.col
            }
        }
        // 找到最深层级是几层
        let deep = 0
        arr.forEach(v => {
            if (v.level > deep) {
                deep = v.level
            }
        })

        for (let level = deep; level > 0; level--) {
            arr.forEach((v, index) => {
                if (v.level == level && v.hasChildren) {
                    let directChildIndex = getDirectChildIndex(arr, v)
                    Object.keys(key2Column).forEach(key => {
                        let c = key2Column[key]
                        let cells = ""
                        directChildIndex.forEach(r => {
                            cells = cells + GC.Spread.Sheets.CalcEngine.rangeToFormula(spread.getActiveSheet().getRange(range.row + r, c, 1, 1)) + ","
                        })
                        cells = cells.slice(0, cells.length - 1)
                        //  将自动求和的部分改为公式求和，以确保在excel中公式可用
                        sheet.setFormula(range.row + index, c, "SUM(" + cells + ")")
                    })
                }
            })
        }
    })


    let searchCondition = new GC.Spread.Sheets.Search.SearchCondition()
    searchCondition.searchTarget = GC.Spread.Sheets.Search.SearchFoundFlags.cellTag
    searchCondition.rowStart = 0
    searchCondition.searchString = "合计"
    let searchResult = sheet.search(searchCondition)
    sheet.setArray(searchResult.foundRowIndex, searchResult.foundColumnIndex + 1, [calcSumAll(searchResult, source, sheet)], true)

    sheet.resumeEvent()
    sheet.resumeCalcService()
    sheet.resumePaint()
}

// 总合计的求和逻辑
function calcSumAll(searchResult, source, sheet) {
    let strs = searchResult.foundString.split("-")
    let table
    sheet.tables.all().forEach(t => {
        if (t.bindingPath() == strs[1]) {
            table = t
        }
    })
    let arr = source[strs[1]]
    let range = table.range()
    let key2Column = {}
    for (let i = 0; i < range.colCount; i++) {
        let field = table.getColumnDataField(i)
        let index = value_keys.indexOf(field)
        if (index > -1) {
            key2Column[value_keys[index]] = i + range.col
        }
    }
    let calcResult = new Array(value_keys.length).fill("SUM(")
    arr.forEach((v, index) => {
        if (v.parent == null) {
            value_keys.forEach(key => {
                let i = value_keys.indexOf(key)
                calcResult[i] = calcResult[i] + GC.Spread.Sheets.CalcEngine.rangeToFormula(spread.getActiveSheet().getRange(range.row + index, key2Column[key], 1, 1)) + ","
            })
        }
    })
    calcResult = calcResult.map(v => {
        v = v.slice(0, v.length - 1)
        v = v + ")"
        return v
    })
    return calcResult

}

// 获取当前数据的直接子级的index
function getDirectChildIndex(arr, item) {
    let res = []
    arr.forEach((v, index) => {
        if (v.parent == item.no) {
            res.push(index)
        }
    })
    return res
}
function addInvalidComments() {
    setTimeout(() => {
        let sheet = spread.getActiveSheet()
        let searchCondition = new GC.Spread.Sheets.Search.SearchCondition()
        searchCondition.searchTarget = GC.Spread.Sheets.Search.SearchFoundFlags.cellTag
        searchCondition.rowStart = 0
        searchCondition.searchString = "$合计$"
        let searchResult = sheet.search(searchCondition)
        sheet.suspendPaint()
        sheet.comments.clear()
        while (true) {
            let colCount = sheet.getColumnCount()
            for (let c = 0; c < colCount; c++) {
                let r = searchResult.foundRowIndex
                let style = sheet.getActualStyle(r, c)
                if (!sheet.isValid(r, c, sheet.getValue(r, c))) {
                    style.backColor = "#c75b12"
                    sheet.setStyle(r, c, style)
                    let tag = sheet.getTag(r, c)
                    if (tag) {
                        let comment = sheet.comments.add(r, c, tag)
                        comment.autoSize(true)
                    }
                } else if (sheet.getDataValidator(r, c)) {
                    style.backColor = "#8497b0"
                    sheet.setStyle(r, c, style)
                }
            }
            searchCondition.rowStart = searchResult.foundRowIndex + 1
            searchResult = sheet.search(searchCondition)
            if (!searchResult.foundString) {
                break
            }
        }

        sheet.resumePaint()
    }, 0);
}

function changeCellYearMonth() {
    document.getElementById("yearMonth").innerText = `当前年份：${tempData.year}    当前月份：${tempData.month}`
    let sheet = spread.getActiveSheet()
    let searchCondition = new GC.Spread.Sheets.Search.SearchCondition()
    searchCondition.searchTarget = GC.Spread.Sheets.Search.SearchFoundFlags.cellText | GC.Spread.Sheets.Search.SearchFoundFlags.cellTag
    searchCondition.rowStart = 0
    sheet.suspendPaint()
    let str_changes = {
        "N-1": {
            replaceTo: tempData.year - 1,
            reg: "N-1"
        },
        "前M月": {
            replaceTo: "1~" + tempData.month + "月",
            reg: "前M月"
        },
        "N年": {
            replaceTo: tempData.year,
            reg: "N"
        }
    }
    Object.keys(str_changes).forEach(str_key => {
        searchCondition.searchString = str_key
        searchCondition.rowStart = 0
        searchCondition.columnStart = 0
        let searchResult = sheet.search(searchCondition)
        while (true) {
            let row = searchResult.foundRowIndex
            let col = searchResult.foundColumnIndex
            if (searchResult.searchFoundFlag == GC.Spread.Sheets.Search.SearchFoundFlags.cellText) {
                let text = sheet.getValue(row, col)
                let reg = new RegExp(str_changes[str_key].reg)
                text = text.replace(reg, str_changes[str_key].replaceTo)
                sheet.setValue(row, col, text)
            } else if (searchResult.searchFoundFlag == GC.Spread.Sheets.Search.SearchFoundFlags.cellTag) {
                let tag = sheet.getTag(row, col)
                let reg = new RegExp(str_changes[str_key].reg)
                tag = tag.replace(reg, str_changes[str_key].replaceTo)
                sheet.setTag(row, col, tag)
            }

            searchCondition.columnStart = searchResult.foundColumnIndex + 1
            searchResult = sheet.search(searchCondition)
            if (!searchResult.foundString) {
                break
            }
        }
    })

    sheet.resumePaint()
}

function lvlCell() {
    this.isOpen = true
    this.typeName = "lvlCell"
}
lvlCell.prototype = new GC.Spread.Sheets.CellTypes.Text()
let oldPaint = GC.Spread.Sheets.CellTypes.Text.prototype.paint
lvlCell.prototype.paint = function () {
    let arg = arguments
    if (this.isOpen) {
        arg[1] = "－ " + arg[1]
    } else {
        arg[1] = "＋ " + arg[1]
    }
    oldPaint.apply(this, arg)
}